
-- 三连冠的队伍
with tmp as(
    select team, 
    year, 
    row_number() over(partition by team order by year) rownum 
    from t1 
    where year is not null)
from tmp
select team,/*year-rownum*/ count(*) cont 
group by team, year-rownum
having cont>=3;


--2、找出每个id在在一天之内所有的波峰与波谷值
--tmp算5分钟差值
with tmp as(
from t2
SELECT id, time, price, 
price-lag(price) over(PARTITION BY id ORDER BY unix_timestamp(`time`, "HH:mm")) diff
),
--tmp2算递增或递减分组
tmp2 as(
from tmp 
select id, time, price, diff, 
if(diff>=0,1,0) up,
if(diff<=0,1,0) down),
--tmp3算递增递减区间分组
tmp3 as (
from tmp2 
select id, time, price, diff,up, down,
    if(up=1,row_number() over(PARTITION BY id ORDER BY unix_timestamp(`time`, "HH:mm"))-row_number() over(PARTITION BY id,up ORDER BY unix_timestamp(`time`, "HH:mm")),-1) upgrp,
    if(down=1,row_number() over(PARTITION BY id ORDER BY unix_timestamp(`time`, "HH:mm"))-row_number() over(PARTITION BY id,down ORDER BY unix_timestamp(`time`, "HH:mm")),-1) downgrp
where diff is not null
order by id, unix_timestamp(`time`, "HH:mm")
),
--tmp4根据分组算极大值极小值
tmp4 as(
from tmp3
    select id, time, price, diff,up, down,upgrp,downgrp,
    max(price) over(PARTITION BY id, upgrp) localmax,
    min(price) over(PARTITION BY id, downgrp) localmin
order by id, unix_timestamp(`time`, "HH:mm")
)
--筛选输出
from tmp4
select 
     id, time, price,/* up, down, diff,localmax,localmin,upgrp,downgrp,*/
     case when up=1 then '波峰'
          when down=1 then '波谷'
     end
     as feature
where 
    (price=localmin and down=1) or 
    (price=localmax and up=1)
;

--3.1、每个id浏览时长、步长 3.2、如果两次浏览之间的间隔超过30分钟，认为是两个不同的浏览时间；再求每个id浏览时长、步长
--3.1
select id, count(*) step, 
round((max(unix_timestamp(dt, "yyyy/MM/dd HH:mm"))-min(unix_timestamp(dt, "yyyy/MM/dd HH:mm")))/60) time
from t3
group by id
;
--3.2

---
with tmp1 as(
from t3
select id, dt, unix_timestamp(dt, "yyyy/MM/dd HH:mm") dt2, 
        unix_timestamp(dt, "yyyy/MM/dd HH:mm")-
        lag(unix_timestamp(dt, "yyyy/MM/dd HH:mm"),1,unix_timestamp(dt, "yyyy/MM/dd HH:mm")) 
        over(partition by id order by unix_timestamp(dt, "yyyy/MM/dd HH:mm"))
        as delta
),
tmp2 as(
from tmp1 
    select id, dt, dt2, delta, sum(if(delta>=1800,1,0)) over(partition by id order by dt2) grp
)
from tmp2
select id, round((max(dt2)-min(dt2))/60,2) time, count(1) step
group by id, grp

